Covid-Tweets

Initial setting

• The database can be downloand from OneDrive

Database part

Geo data and April data collections

• Note: This chunk needn’t run again.

# Select all tweets in April 2020
if(F){
paste("CREATE TABLE CoronavirusTweets",
      " AS SELECT * FROM CoronavirusTweetsCsv",
      " WHERE (strftime('%Y-%m-%d %H:%M:%S',created_at)>=",
      "strftime('%Y-%m-%d %H:%M:%S','2020-03-29 00:00:00'))",
      " AND (strftime('%Y-%m-%d %H:%M:%S',created_at)<=",
      "strftime('%Y-%m-%d %H:%M:%S','2020-04-29 23:59:59'))",sep='')%>%
  dbSendQuery(conn,.)
  
April_tweet=paste("SELECT Tweet_ID FROM CoronavirusTweets",sep='')%>%
  dbGetQuery(conn,.)
# Set Twitter developer account
create_token(app='MSSP-An-Auxiliary-Tool',
             consumer_key='ORvbA3CEOP06hi9MHfz7yknwV',
             consumer_secret='nAy2PRkiV4AYZ0NvHAF6Iw0IBFrttWMKTuxXbUWN4bcZnMpTQR',
             access_token='1328377313562509313-j1iSFuJLLo3FL768jdnHKe1fzmcWnS',
             access_secret='oJIhGoThBNSBSMLQBo3AxS5kcLrqq8sCjx6OIPX9NRmPT')
for(i in 1:ceiling(nrow(April_tweet)/90000)) {
  rl=rate_limit("lookup_statuses")
  if(rl%>%select(remaining)!=900){
    rl%>%select(reset)*60%>%ceiling()%>%Sys.sleep()
  }
  april_tweet=lookup_statuses(April_tweet$Tweet_ID[(900*i):nrow(April_tweet)])
  if(i==1){April_tweet=april_tweet}else{April_tweet=rbind(April_tweet,april_tweet)}
}
  April_tweet%>%
  select(status_id,user_id,screen_name,created_at,text,is_quote,
         is_retweet,favourites_count,retweet_count,followers_count,
         friends_count,lang)%>%
  dbWriteTable(conn,'CoronavirusTweets',.)
}
# Select all tweets with geo information from 202001 to 202011
if(F){
paste("CREATE TABLE CoronavirusTweetsGeo",
      " AS SELECT * FROM CoronavirusTweetsCsv",
      " WHERE Geolocation_coordinate='YES'",sep='')%>%
  dbSendQuery(conn,.)

Geo_tweet=paste("SELECT Tweet_ID FROM CoronavirusTweetsGeo",sep='')%>%
  dbGetQuery(conn,.)


for(i in 1:ceiling(nrow(Geo_tweet)/90000)) {
  rl=rate_limit("lookup_statuses")
  if(rl%>%select(remaining)!=900){
    rl%>%select(reset)*60%>%ceiling()%>%Sys.sleep()
  }
  geo=lookup_statuses(Geo_tweet$Tweet_ID[(900*i):nrow(Geo_tweet)])
  if(i==1){Geo=geo}else{Geo=rbind(Geo,geo)}
}

lat_lng(Geo)%>%
  select(status_id,user_id,screen_name,created_at,text,is_quote,
         is_retweet,favourites_count,retweet_count,followers_count,
         friends_count,lang,place_full_name,place_type,country_code,
         place_name,country,lat,lng)%>%
  dbWriteTable(conn,'CoronavirusTweetsGeo',.)

# Delete initial collection of covid tweets csv files table
"DROP TABLE CoronavirusTweetsCsv" %>%
  dbSendQuery(conn,.)
# Create index to accelerate query
paste("CREATE INDEX CT_status_id ON CoronavirusTweets(status_id);",
  "CREATE INDEX CTG_status_id ON CoronavirusTweetsGeo(status_id);",
  "CREATE INDEX TS_status_id ON TweetsSentiment(status_id);",
  "CREATE INDEX TGS_status_id ON TweetsGeoSentiment(status_id);",
  "CREATE INDEX CTG_lat_long ON CoronavirusTweetsGeo(lat,lng);",
  "CREATE UNIQUE INDEX GD_lat_long ON GeoDetail(lat,lng)")%>%
  dbSendQuery(conn,.)
}
dbDisconnect(conn)

Get data function

getTwitterData=function(conn,geoinfo=T,keywords=NULL,
                        period=c('2020-03-29 00:00:00','2020-04-30 23:59:59')){
  # Select table of database according to 'geoinfo'
  if(geoinfo){
    geoinfo_query=paste("SELECT CoronavirusTweetsGeo.*,",
                        "city,state,country,sentiment_score ",
                        "FROM CoronavirusTweetsGeo ",
                        "LEFT JOIN TweetsGeoSentiment ON ",
                        "CoronavirusTweetsGeo.status_id=",
                        "TweetsGeoSentiment.status_id ",
                        "LEFT JOIN GeoDetail ON ",
                        "CoronavirusTweetsGeo.lat=GeoDetail.lat ",
                        "AND CoronavirusTweetsGeo.lng=GeoDetail.lng",sep="")
  }
  else{
    geoinfo_query=paste("SELECT CoronavirusTweets.*,sentiment_score ",
                        "FROM CoronavirusTweets ",
                        "LEFT JOIN TweetsSentiment ON ",
                        "CoronavirusTweets.status_id=",
                        "TweetsSentiment.status_id",sep="")
  }
  # Add keywords conditions according to 'keywords' 
  if(length(keywords==0)){
    keywords_query=''
  }
  else{
    for(i in 1:length(keywords)){
      if(i==1){
        keywords_query=paste(" ((text LIKE '%",keywords[i],"%')",sep="")
      }
      else{
        keywords_query=keywords_query%>%
          paste("OR (text LIKE '%",keywords[i],"%')",sep="")
      }
    }
    keywords_query=paste(keywords_query,") ",sep="")
  }
  # Add period conditions according to 'period'
  if(length(period)!=2){
    period_query=''
  }
  else{
    period_query=paste(" (strftime('%Y-%m-%d %H:%M:%S',created_at)>=",
                       "strftime('%Y-%m-%d %H:%M:%S','",period[1],"') ",
                       "AND strftime('%Y-%m-%d %H:%M:%S',created_at)<=",
                       "strftime('%Y-%m-%d %H:%M:%S','",period[2],"')) ",
                       sep="")
  }
  # Write SQL
  if(period_query==''){
    if(keywords_query==''){
      query=paste(geoinfo_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",keywords_query,sep="")
    }
  }
  else{
    if(keywords_query==''){
      query=paste(geoinfo_query," WHERE",period_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",
                  period_query,"AND",keywords_query,sep="")
    }
  }
  # Obtain Data
 dbGetQuery(conn,query)
}

Get trend function

getTwitterTrend=function(conn,geoinfo='country',trend='day',keywords=NULL,
                       period=c('2020-03-29 00:00:00','2020-04-30 23:59:59')){
  # Add trend cconditions according to 'trend'
  if(trend=='day'){
    trend_query=c("'%Y-%m-%d'","date")
  }
  else{
    if(trend=='week'){
      trend_query=c("'%W'","week")
    }
    else{
      if(trend=='month'){
        trend_query=c("'%m'","month")
      }
      else{
        stop("The trend can only be 'day', 'week' or 'month'.") 
      }
    }
  }
    # Select table of database according to 'geoinfo'
  if(is.null(geoinfo)){
    geoinfo_query=paste("SELECT strftime(",trend_query[1],
                        ",created_at) AS ",trend_query[2],", ",
                        "count(*) AS number, ",
                        "avg(sentiment_score) AS sentiment_score ",
                        "FROM CoronavirusTweets ",
                        "LEFT JOIN TweetsSentiment ON ",
                        "CoronavirusTweets.status_id=",
                        "TweetsSentiment.status_id",sep="")
    group_query=paste(" GROUP BY strftime(",trend_query[1],
                      ",created_at)",sep="")
  }
  else{
    if(geoinfo=='country'){
    geoinfo_query=paste("SELECT strftime(",trend_query[1],
                        ",created_at) AS ",trend_query[2],", ",
                        "count(*) AS number, country, ",
                        "avg(sentiment_score) AS sentiment_score ",
                        "FROM CoronavirusTweetsGeo ",
                        "LEFT JOIN TweetsGeoSentiment ON ",
                        "CoronavirusTweetsGeo.status_id=",
                        "TweetsGeoSentiment.status_id ",
                        "LEFT JOIN GeoDetail ON ",
                        "CoronavirusTweetsGeo.lat=GeoDetail.lat ",
                        "AND CoronavirusTweetsGeo.lng=GeoDetail.lng",sep="")
    group_query=paste(" GROUP BY strftime(",trend_query[1],
                      ",created_at),country",sep="")
    }
    else{
      if(geoinfo=='state'){
        geoinfo_query=paste("SELECT strftime(",trend_query[1],
                            ",created_at) AS ",trend_query[2],", ",
                            "count(*) AS number, country, state, ",
                            "avg(sentiment_score) AS sentiment_score ",
                            "FROM CoronavirusTweetsGeo ",
                            "LEFT JOIN TweetsGeoSentiment ON ",
                            "CoronavirusTweetsGeo.status_id=",
                            "TweetsGeoSentiment.status_id ",
                            "LEFT JOIN GeoDetail ON ",
                            "CoronavirusTweetsGeo.lat=GeoDetail.lat ",
                            "AND CoronavirusTweetsGeo.lng=GeoDetail.lng",sep="")
        group_query=paste(" GROUP BY strftime(",trend_query[1],
                          ",created_at),country,state",sep="")
      }
      else{
        if(geoinfo=='city'){
          geoinfo_query=paste("SELECT strftime(",trend_query[1],
                              ",created_at) AS ",trend_query[2],", ",
                              "count(*) AS number, country, state, city, ",
                              "avg(sentiment_score) AS sentiment_score ",
                              "FROM CoronavirusTweetsGeo ",
                              "LEFT JOIN TweetsGeoSentiment ON ",
                              "CoronavirusTweetsGeo.status_id=",
                              "TweetsGeoSentiment.status_id ",
                              "LEFT JOIN GeoDetail ON ",
                              "CoronavirusTweetsGeo.lat=GeoDetail.lat ",
                              "AND CoronavirusTweetsGeo.lng=GeoDetail.lng",
                              sep="")
           group_query=paste(" GROUP BY strftime(",trend_query[1],
                             ",created_at),country,state,city",sep="")
        }
        else{
          stop("The geoinfo can only be 'NULL', 'city', 'state' or 'country'.")
        }
      }
    }
  }
  
  # Add keywords conditions according to 'keywords' 
  if(is.null(keywords)){
    keywords_query=''
  }
  else{
    for(i in 1:length(keywords)){
      if(i==1){
        keywords_query=paste(" ((text LIKE '%",keywords[i],"%')",sep="")
      }
      else{
        keywords_query=keywords_query%>%
          paste("OR (text LIKE '%",keywords[i],"%')",sep="")
      }
    }
    keywords_query=paste(keywords_query,") ",sep="")
  }
  # Add period conditions according to 'period'
  if(is.null(period)){
    period_query=''
  }
  else{
    if(length(period)==2){
          period_query=paste(" (strftime('%Y-%m-%d %H:%M:%S',created_at)>=",
                       "strftime('%Y-%m-%d %H:%M:%S','",period[1],"') ",
                       "AND strftime('%Y-%m-%d %H:%M:%S',created_at)<=",
                       "strftime('%Y-%m-%d %H:%M:%S','",period[2],"')) ",
                       sep="")
    }
    else{
      stop("The time period should be a vector with length 2.") 
    }
  }
  # Write SQL
  if(period_query==''){
    if(keywords_query==''){
      query=paste(geoinfo_query,group_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",keywords_query,group_query,sep="")
    }
  }
  else{
    if(keywords_query==''){
      query=paste(geoinfo_query," WHERE",period_query,group_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",period_query,"AND",keywords_query,
                  group_query,sep="")
    }
  }
  # Obtain Data
 dbGetQuery(conn,query)
}

 # Add keywords conditions according to 'keywords' 
  if(is.null(keywords)){
    keywords_query=''
  }
  else{
    for(i in 1:length(keywords)){
      if(i==1){
        keywords_query=paste(" ((text LIKE '%",keywords[i],"%')",sep="")
      }
      else{
        keywords_query=keywords_query%>%
          paste("OR (text LIKE '%",keywords[i],"%')",sep="")
      }
    }
    keywords_query=paste(keywords_query,") ",sep="")
  }
  # Add period conditions according to 'period'
  if(is.null(period)){
    period_query=''
  }
  else{
    if(length(period)==2){
      period_query=paste(" (strftime('%Y-%m-%d %H:%M:%S',created_at)>=",
                         "strftime('%Y-%m-%d %H:%M:%S','",period[1],"') ",
                         "AND strftime('%Y-%m-%d %H:%M:%S',created_at)<=",
                         "strftime('%Y-%m-%d %H:%M:%S','",period[2],"')) ",
                         sep="")
    }
    else{
      stop("The time period should be a vector with length 2.") 
    }
  }
  # Write SQL
  if(period_query==''){
    if(keywords_query==''){
      query=paste(geoinfo_query,group_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",keywords_query,group_query,sep="")
    }
  }
  else{
    if(keywords_query==''){
      query=paste(geoinfo_query," WHERE",period_query,group_query,sep="")
    }
    else{
      query=paste(geoinfo_query," WHERE",period_query,"AND",keywords_query,
                  group_query,sep="")
    }
  }
  # Obtain Data
  dbGetQuery(conn,query)
}

# connect to database

dbpath="/Users/mac/Desktop/Covid-tweets-en.db"
conn=dbConnect(SQLite(),dbpath)

# load spread data
spread_data=read.csv("/Users/mac/Desktop/shiny/us_covid19_daily.csv",header=TRUE)%>%
  transmute(date=ymd(date),
            positiveIncrease=positiveIncrease,
            deathIncrease=deathIncrease)%>%
  arrange(desc(date))%>%
  .[68:100,]

Geoplot=getTwitterTrend(conn,geoinfo='state',period=NULL)%>%
  filter(country=='United States')%>%
  {merge(read.csv("/Users/mac/Desktop/shiny/us_states_daily.csv",header=TRUE),.,by="state")}%>%
  {mutate(.,hover=with(.,paste(state,
                            "<br> <br> Positive:",positive,
                            "<br> death:",death,
                            "<br> number of tweets",number,
                            "<br> sentiment score of this state",sentiment_score)))}

ay=list(tickfont=list(color="red"),overlaying="y",side="right",title="frequency")

g <- list(
  scope = 'usa',
  projection = list(type = 'albers usa'),
  showlakes = TRUE,
  lakecolor = toRGB('white'))

Visualization

#data1
 fig=plot_ly(data_1,x=~date,y=~positiveIncrease,color=I('black'),
                name='positiveIncrease',type ='scatter',mode='lines+markers') 
    n=nrow(data_1)
    color=data$sentiment%>%
      {(.[1:(n-1)]+.[2:n])/2}%>%
      {ifelse(.>0,'green','red')}
    
    fig=fig%>%
      add_trace(x=data_1$date[1:2],y=data_1$frequency[1:2],color=I('blue'),
                name=input[1],mode='lines+markers',yaxis="y2",
                visible='legendonly')
    for(i in 1:(n-1))
      fig=fig%>%
        add_trace(x=data_1$date[i:(i+1)],y=data_1$frequency[i:(i+1)],color=I(color[i]),
                  marker=list(symbol = 2,size=10),showlegend=F,mode='lines+markers',yaxis="y2")
    fig%>%layout(title="Twitter Sentiment trends",yaxis2=ay,xaxis=list(title="x"))

    

Geom plots